package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.stereotype.Repository;

import com.ws.shop.dao.VerificationDao;
import com.ws.shop.entity.Verification;
import com.ws.shop.util.JdbcUtil;

@Repository(value="verificationDao")
public class VerificationDaoImpl extends BaseDaoImpl implements  VerificationDao{
	
	private static Logger log = Logger.getLogger(VerificationDaoImpl.class);
	
	public int repaySetpwd(final String code,final String type,final String password){
		return (Integer)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							connect.setAutoCommit(false);
							String s = "select mail from jvf_verification v where v.code = '" + code + "' for update" ;
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							if(rs.next()){
								String mail = rs .getString(1);
								int intdel = delCode(connect,mail,type);
								int intp = setpwd(connect,mail,password);
								if(intdel>0&&intp>0){
									connect.commit();
									return 0;
								}else{
									connect.rollback();
									return 2;//操作失败
								}

							}else{
								connect.rollback();
								return 1;// 不存在
							}
						}catch (Exception e) {
							e.printStackTrace();
							connect.rollback();
							log.error(e.getMessage(), e);
							return  -1;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
	}
	
	
	public List<Map<String,Object>> getVerification(String type ,String code){
		String s = "select v.id,v.mail,v.type,v.code, v.count  from jvf_verification v where v.type = ? and v.code = ? ";
		return this.queryForList(s);
	}
	
	//激活
	public int verficaty(final String type,final String code,final String username){
		return (Integer)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							connect.setAutoCommit(false);
							String s = "select mail from jvf_verification v where v.code = '" + code + "' and v.type='" + type + "' for update" ;
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							if(rs.next()){
								String mail = rs .getString(1);
								JdbcUtil.close(rs, stmt, null);
								s = "select acttime from jvf_user where name = '" + username + "' for update ";
								stmt = connect.createStatement();
								rs = stmt.executeQuery(s);
								if(rs.next()){
									Date acttime = rs.getTimestamp(1);
									if(acttime!=null){//已经激活
										connect.rollback();
										return 3;
									}else{
										JdbcUtil.close(rs, stmt, null);
										s = "update jvf_user set acttime = now() where name = '" + username + "'";
										stmt = connect.createStatement();
										int update = stmt.executeUpdate(s);
										JdbcUtil.close(null, stmt, null);
										if(update>0){
											connect.commit();
											return 0;
										}else{
											connect.rollback();
											return 2;//操作失败
										}
									}
								}else{
									connect.rollback();
									return 4;// 用户不存在
								}
							}else{
								connect.rollback();
								return 1;// 不存在
							}
						}catch (Exception e) {
							e.printStackTrace();
							connect.rollback();
							log.error(e.getMessage(), e);
							return  -1;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
	}
	
	/**
	 * 删除 
	 * @param connect
	 * @param mail
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	public int delCode(Connection connect,String mail,String type) throws SQLException{
		String s = "delete from jvf_verification where type = ? and mail =  ? ";
		PreparedStatement ps = connect.prepareStatement(s);
		ps.setString(1, type);
		ps.setString(2, mail);
		int c = ps.executeUpdate();
		return c;
	}
	
	/**
	 * 
	 * 设置密码
	 * @param connect
	 * @param mail
	 * @return
	 * @throws SQLException
	 * 
	 */
	public int setpwd(Connection connect,String mail,String password) throws SQLException{
		String s = "update jvf_user set password = ? where mail = ?";
		PreparedStatement ps = connect.prepareStatement(s);
		ps.setString(1, password);
		ps.setString(2, mail);
		int c = ps.executeUpdate();
		return c;
	}
	
	public int saveCode(Connection connect,Verification v) throws SQLException{
		String s = "insert into jvf_verification(mail,type,addtime,count,code) values(?,?,now(),?,?)";
		PreparedStatement ps = connect.prepareStatement(s);
		ps.setString(1, v.getMail());
		ps.setString(2, v.getType());
		ps.setInt(3, v.getCount());
		ps.setString(4, v.getCode());
		int c = ps.executeUpdate();
		return c;
	}
	
	
	public int getpwd(final String mail,final String type,final String code){
		return (Integer)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							connect.setAutoCommit(false);
							String s = "select v.count  from jvf_verification v where to_days(v.addtime) = to_days(now())  and v.mail = '"+mail+"' and  v.type = '"+type+"'" ;
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							if(rs.next()){
								int c = rs .getInt(1);
								int intdel = delCode(connect,mail,type);
								Verification v = new Verification();
								v.setCount(c+1);
								v.setMail(mail);
								v.setType(type);
								v.setCode(code);
								int save = saveCode(connect, v);
								if(intdel>0 && save>0){
									connect.commit();
									return 0;
								}else{
									connect.rollback();
									return 2;//操作失败
								}

							}else{
								connect.rollback();
								return 1;// 不存在
							}
						}catch (Exception e) {
							e.printStackTrace();
							connect.rollback();
							log.error(e.getMessage(), e);
							return  -1;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
	}
	
	
}
